package com.hunan.api.catv.service.biz.jdbc;

import com.github.pagehelper.PageHelper;
import com.github.pagehelper.PageInfo;
import com.hunan.api.catv.service.ResultService;
import com.hunan.api.catv.utils.JdbcUtils;
import lombok.extern.slf4j.Slf4j;
import org.springframework.stereotype.Service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.util.ArrayList;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

/**
 * @Author: wangchao
 * @Date: 2020/10/29 16:38
 */
@Service
@Slf4j
public class JdbcBiz extends ResultService {


    public PageInfo<Object> getListBysql(String sql, int pageNum, int pageSize) {
        //只允许查询sql
        String lowSql = sql.toLowerCase();
        List<Object> records = new ArrayList();
        PageInfo<Object> pageInfo = null;
        if (lowSql.contains("update") || lowSql.contains("insert") || lowSql.contains("delete")) {
            return new PageInfo<>(records);
        }
        records = getList(sql);
        if (pageNum == 0 || pageSize == 0) {
            pageInfo = new PageInfo<>(records);
        } else {
            //总记录数
            int total = records.size();
            // 开始索引
            int fromIndex = (pageNum - 1) * pageSize;
            // 结束索引
            int toIndex = fromIndex + pageSize;
            // 如果结束索引大于集合的最大索引，那么规定结束索引=集合大小
            if (toIndex > total) {
                toIndex = total;
            }
            PageHelper.startPage(pageNum, pageSize);
            List<Object> list = new ArrayList<>();
            if (fromIndex <= total) {
                list = records.subList(fromIndex, toIndex);
            }
            pageInfo = new PageInfo<>(list);
            pageInfo.setTotal(total);
        }

        return pageInfo;
    }

    private List<Object> getList(String sql) {
        List<Object> records = new ArrayList();
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        ResultSetMetaData rsmd = null;
        try {
            conn = JdbcUtils.getConn();
            //进行预编译，这里进行参数设置
            pstmt = conn.prepareStatement(sql);
            //进行编译
            rs = pstmt.executeQuery();
            rsmd = rs.getMetaData();
            int fieldCount = rsmd.getColumnCount();
            while (rs.next()) {
                Map<String, String> valueMap = new LinkedHashMap<String, String>();
                for (int i = 1; i <= fieldCount; i++) {
                    String fieldClassName = rsmd.getColumnClassName(i);
                    String fieldName = rsmd.getColumnName(i);
                    JdbcUtils.recordMappingToMap(fieldClassName, fieldName, rs, valueMap);
                }
                records.add(valueMap);
            }
        } catch (Exception e) {
            log.error("{}", e);
        } finally {
            JdbcUtils.close();
        }
        return records;
    }

}
